﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
using Telerik.Web.UI;
using System.IO;
using System.Web.UI.WebControls;

namespace NCC_SOP.app_codes
{
    public class webUtilities
    {
        public void checkSession(HttpSessionStateBase session)
        {
            
        }

        public string getPDFURLbyRptID(string rptID)
        {
            string result = "";
            dbConnection db = new dbConnection();
            using (SqlConnection SqlConnection = new SqlConnection(db.connectionStr))
            {
                string selectSQL = "select fileName from metaDisplayView where tableID = " + rptID;
                SqlCommand cmd = new SqlCommand(selectSQL, SqlConnection);
                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    result = reader["fileName"].ToString();
                }
                reader.Close();
            }

            //filter empty string
            if (result.Trim().Equals(""))
            {
                result = "NA";
            }

            return result;
        }


        public bool insertTableAttributeValues(int tabelID, int attributeID, string selectedValue)
        {
            bool result = false;
            dbConnection db = new dbConnection();
            using (SqlConnection SqlConnection = new SqlConnection(db.connectionStr))
            {
                string selectSQL = "INSERT INTO [ncc-sop].[dbo].[MetadataTableAttributeValues]([TableId],[TableAttributeId],[TableAttributeValue]) VALUES(@TableId,@TableAttributeId,@TableAttributeValue)";

                SqlCommand cmd = new SqlCommand(selectSQL, SqlConnection);
                cmd.Parameters.Add("@TableId", SqlDbType.Int);
                cmd.Parameters.Add("@TableAttributeId", SqlDbType.Int);
                cmd.Parameters.Add("@TableAttributeValue", SqlDbType.VarChar);

                cmd.Parameters["@TableId"].Value = tabelID;
                cmd.Parameters["@TableAttributeId"].Value = attributeID;
                cmd.Parameters["@TableAttributeValue"].Value = selectedValue;

                try
                {

                    /*
                    string query = cmd.CommandText;
                    foreach (SqlParameter p in cmd.Parameters)
                    {
                        query = query.Replace(p.ParameterName, p.Value.ToString());
                    }
                    Console.Write(query);
                    **/

                    SqlConnection.Open();
                    cmd.ExecuteNonQuery();
                    result = true;
                }
                catch (Exception e2)
                {
                    Console.Write(e2.StackTrace);
                }

                return result;
            }
        }

        public string BindValidResults(Telerik.Web.UI.RadUpload RadUpload1, string targetFolder,
            Repeater repeaterValidResults, Label uploadError)
        {
            string output = "";
            if (RadUpload1.UploadedFiles.Count > 0)
            {
                foreach (UploadedFile validFile in RadUpload1.UploadedFiles)
                {
                    try
                    {
                        validFile.SaveAs(Path.Combine(targetFolder, validFile.GetName()), true);
                        output = Path.Combine("staticReports/", validFile.GetName());
                    }
                    catch (Exception e3)
                    {
                        Console.Write(e3.StackTrace);
                    }
                }
                repeaterValidResults.Visible = true;
                repeaterValidResults.DataSource = RadUpload1.UploadedFiles;
                repeaterValidResults.DataBind();
                uploadError.Visible = false;
            }
            else
            {
                uploadError.Text = "Your files contain errors, pls check and do it again.";
                uploadError.Visible = true;
                repeaterValidResults.Visible = false;
            }
            return output;
        }

        public void loadDepartmentAccessRights(CheckBoxList cb)
        {
            dbConnection db = new dbConnection();
            using (SqlConnection SqlConnection = new SqlConnection(db.connectionStr))
            {
                SqlCommand cmd = new SqlCommand("select * from departments", SqlConnection);

                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ListItem item = new ListItem();
                    item.Text = (reader["alias"].ToString());
                    item.Value = (reader["departmentID"].ToString());
                    cb.Items.Add(item);
                }
            }
        }


        public bool updateRptRoles(string rptID, CheckBoxList cb)
        {
            bool result = false; 
            try
            {
                //update role assignment
                //step 1:delete all records in the roleReportMapping table
                deleteRowAssignment(rptID);

                //step 2: insert new roleReport Mapping records
                for (int i = 0; i < cb.Items.Count; i++)
                {
                    ListItem listItem = cb.Items[i];
                    if (listItem.Selected)
                    {
                        insertRowAssignment(cb.Items[i].Value, rptID);
                    }
                }
                result = true;
            }
            catch (Exception e3) { }
            return result;
        }

        public bool updateRptDepartment(string rptID, CheckBoxList cb)
        {
            bool result = false;
            try
            {
                //update role assignment
                //step 1:delete all records in the roleReportMapping table
                deleteDepartmentAssignment(rptID);

                //step 2: insert new roleReport Mapping records
                for (int i = 0; i < cb.Items.Count; i++)
                {
                    ListItem listItem = cb.Items[i];
                    if (listItem.Selected)
                    {
                        insertDepartmentAssignment(cb.Items[i].Value, rptID);
                    }
                }
                result = true;
            }
            catch (Exception e3) { }
            return result;
        }




        private void deleteRowAssignment(string tableID)
        {
            dbConnection db = new dbConnection();
            using (SqlConnection SqlConnection = new SqlConnection(db.connectionStr))
            {
                string selectSQL = "delete from roleReportMapping where [tableID] = " + tableID;
                SqlCommand cmd = new SqlCommand(selectSQL, SqlConnection);
                SqlConnection.Open();
                cmd.ExecuteNonQuery();
            }
        }
        private void insertRowAssignment(string roleID, string tableID)
        {
            dbConnection db = new dbConnection();
            using (SqlConnection SqlConnection = new SqlConnection(db.connectionStr))
            {
                string selectSQL = "insert into roleReportMapping([roleID],[tableID],[createdBy],[createdOn]) values (@roleID,@tableID,@createdBy,@createdOn) ";
                SqlCommand cmd = new SqlCommand(selectSQL, SqlConnection);
                cmd.Parameters.Add("@roleID", SqlDbType.Int);
                cmd.Parameters.Add("@tableID", SqlDbType.Int);
                cmd.Parameters.Add("@createdBy", SqlDbType.VarChar);
                cmd.Parameters.Add("@createdOn", SqlDbType.DateTime);

                cmd.Parameters["@roleID"].Value = roleID;
                cmd.Parameters["@tableID"].Value = tableID;
                cmd.Parameters["@createdBy"].Value = "admin";
                DateTime currDate = DateTime.Now;
                cmd.Parameters["@createdOn"].Value = currDate.Date.ToString("yyyy-MM-dd HH:mm:ss");

                SqlConnection.Open();
                cmd.ExecuteNonQuery();
            }
        }

        private void deleteDepartmentAssignment(string tableID)
        {
            dbConnection db = new dbConnection();
            using (SqlConnection SqlConnection = new SqlConnection(db.connectionStr))
            {
                string selectSQL = "delete from reportDepartmentMapping where [reportID] = " + tableID;
                SqlCommand cmd = new SqlCommand(selectSQL, SqlConnection);
                SqlConnection.Open();
                cmd.ExecuteNonQuery();
            }
        }
        private void insertDepartmentAssignment(string departmentID, string reportID)
        {
            dbConnection db = new dbConnection();
            using (SqlConnection SqlConnection = new SqlConnection(db.connectionStr))
            {
                string selectSQL = "insert into reportDepartmentMapping([departmentID],[reportID],[createdBy],[createdDt],[status]) values (@departmentID,@reportID,@createdBy,@createdOn,@status) ";
                SqlCommand cmd = new SqlCommand(selectSQL, SqlConnection);
                cmd.Parameters.Add("@departmentID", SqlDbType.Int);
                cmd.Parameters.Add("@reportID", SqlDbType.Int);
                cmd.Parameters.Add("@createdBy", SqlDbType.VarChar);
                cmd.Parameters.Add("@createdOn", SqlDbType.DateTime);
                cmd.Parameters.Add("@status", SqlDbType.VarChar);

                cmd.Parameters["@departmentID"].Value = departmentID;
                cmd.Parameters["@reportID"].Value = reportID;
                cmd.Parameters["@createdBy"].Value = "admin";
                DateTime currDate = DateTime.Now;
                cmd.Parameters["@createdOn"].Value = currDate.Date.ToString("yyyy-MM-dd HH:mm:ss");
                cmd.Parameters["@status"].Value = "active";

                SqlConnection.Open();
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e) { Console.Write(e.ToString()); }
            }
        }


    }
}